package com.buaa.service.impl;

import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.solr.client.solrj.SolrClient;
import org.apache.solr.client.solrj.SolrQuery;
import org.apache.solr.client.solrj.response.Group;
import org.apache.solr.client.solrj.response.GroupCommand;
import org.apache.solr.client.solrj.response.GroupResponse;
import org.apache.solr.client.solrj.response.QueryResponse;
import org.apache.solr.common.SolrDocument;
import org.apache.solr.common.SolrDocumentList;
import org.apache.solr.common.params.GroupParams;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.buaa.dao.BaseDao;
import com.buaa.service.BaseService;
import com.buaa.utils.JacksonUtil;
import com.buaa.utils.SolrUtil;

@Service
public class BaseServiceImpl implements BaseService {

	@Autowired
	private BaseDao baseDao;

	@Override
	public String sumInfo(String loctype) {
		String sql = null;
		switch (loctype) {
		case "province":
			sql = "SELECT '黑龙江省',COUNT(*),128.04741,47.35659 FROM base";
			break;
		case "city":
			sql = "SELECT city,COUNT(*),city.lng,city.lat FROM base ins,city WHERE ins.city = city.city_name GROUP BY city,city.lng,city.lat";
			break;
		case "xian":
			sql = "SELECT county,COUNT(*),county.lng,county.lat FROM base ins,county WHERE ins.county = county.county_name GROUP BY county,county.lng,county.lat";
			break;
		case "danwei":
			sql = "SELECT ins2.name,COUNT(*),ins2.lng,ins2.lat FROM base ins,institution ins2 WHERE ins.unit_code = ins2.code GROUP BY ins2.name,ins2.lng,ins2.lat";
			break;
		}
		List<Object[]> list = baseDao.common2(sql);
		String res = "";
		Object[] objects = null;
		if (list != null) {
			for (int i = 0; i < list.size(); i++) {
				objects = list.get(i);
				if (i == list.size() - 1) {
					res += objects[2] + "," + objects[3] + "," + objects[1] + "," + objects[0] + "," + loctype;
				} else {
					res += objects[2] + "," + objects[3] + "," + objects[1] + "," + objects[0] + "," + loctype + "|";
				}
			}
		}
		return res;
	}

	@Override
	public Map<String, Long> getCount(String quyu, String loctype, String zhibiao) {
		String sql = "select count(*),base." + zhibiao + " from base ";
		if (loctype.equals("city"))
			sql += " where city='" + quyu + "'";
		else if (loctype.equals("xian"))
			sql += " where county='" + quyu + "'";
		else if (loctype.equals("danwei"))
			sql += ",institution where base.unit_code = institution.code and institution.name='" + quyu + "'";
		sql += " group by base." + zhibiao + " order by count(*) desc ";
		// System.out.println(sql);

		Map<String, Long> metaData = new LinkedHashMap<String, Long>();
		List<Object[]> list = baseDao.common2(sql);
		if (list == null)
			return metaData;
		for (Object[] objects : list) {
			metaData.put(objects[1].toString(), Math.round(Double.parseDouble(objects[0].toString())));
		}
		return metaData;
	}

	@Override
	public String getCitySum() {
		String sql = "SELECT ins2.city,count(*) FROM base ins1,institution ins2 WHERE ins1.unit_code = ins2.code GROUP BY ins2.city";
		List<Object[]> list = baseDao.common2(sql);
		Set<Map<String, Object>> set = new TreeSet<Map<String, Object>>(new Comparator<Map<String, Object>>() {
			@Override
			public int compare(Map<String, Object> o1, Map<String, Object> o2) {
				try {
					int i = Integer.valueOf(o1.get("value").toString()).compareTo(Integer.valueOf(o2.get("value").toString()));
					return i > 0 ? 1 : i < 0 ? -1 : 1;
				} catch (Exception e) {
					e.printStackTrace();
					return 1;
				}
			}
		});
		Map<String, Object> map;
		Set<String> citySet = new HashSet<String>();
		citySet.add("哈尔滨市");
		citySet.add("牡丹江市");
		citySet.add("鸡西市");
		citySet.add("佳木斯市");
		citySet.add("齐齐哈尔市");
		citySet.add("大庆市");
		citySet.add("鹤岗市");
		citySet.add("绥化市");
		citySet.add("黑河市");
		citySet.add("七台河市");
		citySet.add("伊春市");
		citySet.add("大兴安岭地区");
		citySet.add("双鸭山市");
		for (Object[] objects : list) {
			String name = objects[0].toString();
			// System.out.println(name);
			// 数据表中有填"其他市"，有没有填的
			if (name.equals("其他市") || name.equals(""))
				continue;
			map = new HashMap<String, Object>();
			Integer value = 0;
			try {
				value = Integer.valueOf(objects[1].toString());
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (citySet.contains(name)) {
				map.put("name", name);
				map.put("value", value);
				set.add(map);
			}
		}
		return JacksonUtil.toJSon(set);
	}

	private String processSql(String sql, String jdmc, String jdmccxfs, String jdjb, String jdlx) {
		if (!jdmc.equals(""))
			if (jdmccxfs.equals("1"))
				sql += " and ins1.name LIKE '" + jdmc + "'";
			else
				sql += " and ins1.name LIKE '%" + jdmc + "%'";
		if (!jdjb.equals(""))
			sql += " and ins1.level like '" + jdjb + "'";
		if (!jdlx.equals(""))
			sql += " and ins1.type like '" + jdlx + "'";
		return sql;
	}

	@Override
	public String contentSearch(String loctype, String jdmc, String jdmccxfs, String jdjb, String jdlx) {
		String res = "";
		String sql = "";
		switch (loctype) {
		case "province":
			sql = "SELECT COUNT(*),'黑龙江省',126.657715,45.773224 FROM base ins1 ";
			sql = processSql(sql, jdmc, jdmccxfs, jdjb, jdlx);
			break;
		case "city":
			sql = "SELECT COUNT(*),ins1.city,city.lng,city.lat FROM instrument ins1,city WHERE ins1.city = city_name ";
			sql = processSql(sql, jdmc, jdmccxfs, jdjb, jdlx);
			sql += " GROUP BY ins1.city,city.lng,city.lat";
			break;
		case "xian":
			sql = "SELECT COUNT(*),ins1.county,county.lng,county.lat FROM instrument ins1,county WHERE ins1.county = county_name ";
			sql = processSql(sql, jdmc, jdmccxfs, jdjb, jdlx);
			sql += " GROUP BY ins1.county,county.lng,county.lat";
			break;
		case "danwei":
			sql = "SELECT COUNT(*),ins2.name,ins2.lng,ins2.lat FROM base ins1,institution ins2 WHERE ins1.unit_code = ins2.code ";
			sql = processSql(sql, jdmc, jdmccxfs, jdjb, jdlx);
			sql += " GROUP BY ins2.name,ins2.lng,ins2.lat";
			break;
		}

		List<Object[]> list = baseDao.common2(sql);
		Object[] objects = null;
		for (int i = 0; i < list.size(); i++) {
			objects = list.get(i);
			if (i != list.size() - 1) {
				res += objects[1] + "," + objects[0] + "," + objects[2] + "," + objects[3] + "," + loctype + "|";
			} else {
				res += objects[1] + "," + objects[0] + "," + objects[2] + "," + objects[3] + "," + loctype;
			}
		}
		return res;
	}

	private SolrQuery processQuery(SolrQuery query, String jdmc, String jdmccxfs, String jdjb, String jdlx, String ssjg, String ssjgcxfs) {
		// 输入查询条件
		if (StringUtils.isNotBlank(jdmc)) {
			switch (jdmccxfs) {
			case "1": // 全匹配
				query.setQuery("base_name_s:" + jdmc);
				break;
			case "2": // 模糊匹配
				query.setQuery("base_name:" + jdmc);
				break;
			case "3": // 局部全匹配
				query.setQuery("base_name_s:*" + jdmc + "*");
				break;
			default:
				query.setQuery("*:*");
				break;
			}
		} else {
			query.setQuery("*:*");
		}
		if (StringUtils.isNotBlank(ssjg)) {
			switch (ssjgcxfs) {
			case "1": // 全匹配
				query.addFilterQuery("institution_name_s:" + ssjg);
				break;
			case "2": // 模糊匹配
				query.addFilterQuery("institution_name:" + ssjg);
				break;
			case "3": // 局部全匹配
				query.addFilterQuery("institution_name_s:*" + ssjg + "*");
				break;
			}
		}
		if (StringUtils.isNotBlank(jdjb))
			query.addFilterQuery("base_level:" + jdjb);
		if (StringUtils.isNotBlank(jdlx))
			query.addFilterQuery("base_type:" + jdlx);
		return query;
	}

	@Override
	public String contentSearch2(String loctype, String jdmc, String jdmccxfs, String jdjb, String jdlx, String ssjg, String ssjgcxfs) {
		SolrClient solrClient = SolrUtil.getHttpSolrClient("base");
		SolrQuery query = new SolrQuery();
		QueryResponse response = null;

		String res = "";
		query = processQuery(query, jdmc, jdmccxfs, jdjb, jdlx, ssjg, ssjgcxfs);
		// 设置分页信息
		query.setStart(0);
		query.setRows(Integer.MAX_VALUE);
		if (StringUtils.equals(loctype, "province")) {
			// 设置显示的Field的域集合
			query.setFields("base_id");
			// 执行查询并返回结果
			System.out.println("contentSearch:province:\t" + query);
			try {
				response = solrClient.query(query);
				solrClient.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			long count = 0L;
			// 获取匹配结果总数
			if (response != null) {
				count = response.getResults().getNumFound();
			}
			return "黑龙江省," + count + ",127.91077,48.734684,province";
		} else {
			String lng = null;
			String lat = null;
			String groupIndex = null;
			switch (loctype) {
			case "city":
				lng = "city_lng";
				lat = "city_lat";
				groupIndex = "institution_city";
				break;
			case "xian":
				lng = "county_lng";
				lat = "county_lat";
				groupIndex = "institution_county";
				break;
			case "danwei":
				lng = "institution_lng";
				lat = "institution_lat";
				groupIndex = "institution_name_s";
				break;
			default:
				return res;
			}
			// 设置显示的Field的域集合
			query.setFields(lng + "," + lat);
			// 设置分类信息 （默认每个group显示一条）
			query.setParam(GroupParams.GROUP, true);
			query.setParam(GroupParams.GROUP_FIELD, groupIndex);
			// 执行查询并返回结果
			System.out.println("contentSearch:" + loctype + ":\t" + query);
			try {
				response = solrClient.query(query);
				solrClient.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (response != null) {
				GroupResponse groupResponse = response.getGroupResponse();
				if (groupResponse != null) {
					List<GroupCommand> groupList = groupResponse.getValues();
					for (GroupCommand groupCommand : groupList) {
						List<Group> groups = groupCommand.getValues();
						for (Group group : groups) {
							System.out.println("contentSearch:类别总数：" + group.getResult().getNumFound());
							SolrDocumentList list = group.getResult();
							res += group.getGroupValue() + "," + group.getResult().getNumFound() + "," + list.get(0).get(lng) + "," + list.get(0).get(lat) + "," + loctype + "|";
						}
					}
				}
			}
			return res;
		}
	}

	@Override
	public List<Object[]> getAllList(String loctype) {
		String sql = null;
		if (loctype.equals("city")) {
			sql = "select city,count(*) from base group by city order by count(*) desc ";
		} else if (loctype.equals("danwei")) {
			sql = "select ins2.name,count(*) from base ins1,institution ins2 where ins1.unit_code=ins2.code  group by ins2.name order by count(*) desc ";
		}
		return baseDao.common2(sql);
	}

	@Override
	public List<Object> getCountList(String quyu, String loctype, String zhibiao) {
		String sql = "select count(*),ins1." + zhibiao + " from base ins1 ";
		if (loctype.equals("city"))
			sql += " where ins1.city='" + quyu + "'";
		else if (loctype.equals("xian"))
			sql += " where ins1.county='" + quyu + "'";
		else if (loctype.equals("danwei"))
			sql += ",institution ins2 where ins1.unit_code=ins2.code and ins2.name='" + quyu + "'";
		sql += " group by ins1." + zhibiao + " order by count(*) desc ";

		System.out.println(sql);
		List<Object[]> countlist = baseDao.common2(sql);
		String name;
		if (zhibiao.equals("level")) {
			name = "级别";
		} else if (zhibiao.equals("type")) {
			name = "类型";
		} else {
			name = "待定";
		}
		List<Object> ret = new ArrayList<>();
		ret.add(name);
		ret.add(countlist);
		return ret;
	}

	@Override
	public Object getCxTable1Info(String jdmc, String jdmccxfs, String jdjb, String jdlx, String quyu, String loctype) {
		String sql = "select ins1.name,ins2.name ins2name,ins1.code,ins2.code ins2code  from base ins1,institution ins2 where ins1.unit_code = ins2.code ";
		if (loctype.equals("city"))
			sql += " and ins1.city = '" + quyu + "'";
		else if (loctype.equals("xian"))
			sql += " and ins1.county  = '" + quyu + "'";
		else if (loctype.equals("danwei"))
			sql += " and ins2.name= '" + quyu + "'";
		if (!jdmc.equals(""))
			if (jdmccxfs.equals("1"))
				sql += " and ins1.name LIKE '" + jdmc + "'";
			else
				sql += " and ins1.name LIKE '%" + jdmc + "%'";
		// if (!ssdw.equals(""))
		// sql += " and ins1.name like '" + ssdw + "'";
		if (!jdjb.equals(""))
			sql += " and ins1.level like '" + jdjb + "'";
		if (!jdlx.equals(""))
			sql += " and ins1.type like '" + jdlx + "'";
		return baseDao.common2(sql);
	}

	@Override
	public Object getCxTable1Info2(String jdmc, String jdmccxfs, String jdjb, String jdlx, String quyu, String loctype, String ssjg, String ssjgcxfs) {
		SolrClient solrClient = SolrUtil.getHttpSolrClient("base");
		SolrQuery query = new SolrQuery();
		// System.out.println(ssjgcxfs);
		// 输入查询条件
		if (StringUtils.isNotBlank(jdmc)) {
			switch (jdmccxfs) {
			case "1": // 全匹配
				query.setQuery("base_name_s:" + jdmc);
				break;
			case "2": // 模糊匹配
				query.setQuery("base_name:" + jdmc);
				break;
			case "3": // 局部全匹配
				query.setQuery("base_name_s:*" + jdmc + "*");
				break;
			default:
				query.setQuery("*:*");
				break;
			}
		} else {
			query.setQuery("*:*");
		}
		// 输入过滤条件
		switch (loctype) {
		case "city":
			query.addFilterQuery("institution_city:" + quyu);
			break;
		case "xian":
			query.addFilterQuery("institution_county:" + quyu);
			break;
		case "danwei":
			// 全匹配
			query.addFilterQuery("institution_name_s:" + quyu);
			break;
		}
		if (!StringUtils.equals(loctype, "danwei") && StringUtils.isNotBlank(ssjg)) {
			switch (ssjgcxfs) {
			case "1": // 全匹配
				query.addFilterQuery("institution_name_s:" + ssjg);
				break;
			case "2": // 模糊匹配
				query.addFilterQuery("institution_name:" + ssjg);
				break;
			case "3": // 局部全匹配
				query.addFilterQuery("institution_name_s:*" + ssjg + "*");
				break;
			}
		}
		if (StringUtils.isNotBlank(jdjb))
			query.addFilterQuery("base_level:" + jdjb);
		if (StringUtils.isNotBlank(jdlx))
			query.addFilterQuery("base_type:" + jdlx);
		// 设置分页信息（使用默认的）
		query.setStart(0);
		query.setRows(Integer.MAX_VALUE);
		// 执行查询并返回结果
		System.out.println("getCxTable1Info:" + query);
		QueryResponse response = null;
		try {
			response = solrClient.query(query);
			solrClient.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		// 获取匹配的所有结果
		List<Object[]> ret = new ArrayList<>();
		Object[] objects = null;
		SolrDocumentList list = response.getResults();
		// 匹配结果总数
		long count = list.getNumFound();
		System.out.println("匹配结果总数:" + count);
		for (SolrDocument doc : list) {
			objects = new Object[4];
			objects[0] = doc.get("base_name");
			objects[1] = doc.get("institution_name");
			objects[2] = doc.get("base_id");
			objects[3] = doc.get("institution_code");
			ret.add(objects);
		}
		return ret;
	}

	@Override
	public List<Object> getTable1Info(String zhibiao, String zhibiaomc, String quyu, String loctype, String page) {
		String sql = "";
		String sqlnum = "";
		if (loctype.equals("province")) {
			sql = "select ins1.name,ins2.name ins2name,ins1.code,ins2.code ins2code from base ins1,institution ins2 where ins1.unit_code = ins2.code ";
			sqlnum = "select count(*)  from base ins1,institution ins2 where ins1.unit_code = ins2.code ";
		} else if (loctype.equals("city")) {
			sqlnum = "select count(*)  from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins1.city ='" + quyu + "'";
			sql = "select ins1.name,ins2.name ins2name,ins1.code,ins2.code ins2code from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins1.city ='" + quyu + "'";
		} else if (loctype.equals("xian")) {
			sqlnum = "select count(*)  from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins1.county ='" + quyu + "'";
			sql = "select ins1.name,ins2.name ins2name,ins1.code,ins2.code ins2code from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins1.county ='" + quyu + "'";
		} else if (loctype.equals("danwei")) {
			sqlnum = "select count(*)  from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins2.name  ='" + quyu + "'";
			sql = "select ins1.name,ins2.name ins2name,ins1.code,ins2.code ins2code from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins2.name  ='" + quyu + "'";
		}
		if (!zhibiao.equals("")) {
			sqlnum += " and ins1." + zhibiao + "='" + zhibiaomc + "'";
			sql += " and ins1." + zhibiao + "='" + zhibiaomc + "'";
		}

		int curpage = 1;
		if (page != null)
			curpage = Integer.valueOf(page);
		// 总页数
		int totalpage = 0;
		// 下一页
		int nextpage = 0;
		// 上一页
		int lastpage = 0;
		// 每页显示条数
		int pagenum = 10;
		// 数据总条数
		int total = 0;
		List<Object> rsnum = baseDao.common1(sqlnum);
		if (rsnum != null) {
			if (Integer.valueOf(rsnum.get(0).toString()) % pagenum != 0)
				totalpage = Integer.valueOf(rsnum.get(0).toString()) / pagenum + 1;
			else
				totalpage = Integer.valueOf(rsnum.get(0).toString()) / pagenum;
			total = Integer.valueOf(rsnum.get(0).toString());
		}
		if (curpage == 1)
			lastpage = 1;
		else
			lastpage = curpage - 1;
		if (curpage < totalpage)
			nextpage = curpage + 1;
		else {
			nextpage = totalpage;
			pagenum = total - pagenum * (totalpage - 1);
		}
		// 显示区间
		int beginnum = (curpage - 1) * 10;
		int endnum = beginnum + pagenum;

		List<Object[]> rs = baseDao.common2(sql);
		List<Object> ret = new ArrayList<>();
		ret.add(totalpage);
		ret.add(total);
		ret.add(curpage);
		ret.add(nextpage);
		ret.add(lastpage);
		ret.add(pagenum);
		ret.add(beginnum);
		ret.add(endnum);
		ret.add(rs);
		return ret;
	}

	@Override
	public List<Object[]> getTable2Info(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append("ins1.name,");
		sb.append("ins1.code,");
		sb.append("ins1.level,");
		sb.append("ins1.type,");
		sb.append("ins1.city,");
		sb.append("ins1.county,");
		sb.append("ins2.name ins2name,");
		sb.append("ins2.code ins2code");
		String sql = "select " + sb.toString() + " from base ins1,institution ins2 where ins2.code=ins1.unit_code and ins1.code='" + id + "'";
		return baseDao.common2(sql);
	}

	@Override
	public List<Object[]> getBaseByInstitution(String id,Integer start) {
		//String sql = "SELECT ins1.id,ins1.name FROM base ins1,institution ins2 WHERE ins1.unit_code = ins2.code AND ins2.code = " + id + " LIMIT 0,10";
		String sql = "SELECT ins1.id,ins1.name FROM base ins1,institution ins2 WHERE ins1.unit_code = ins2.code AND ins2.code = " + id + " LIMIT "+start+",5";
		return baseDao.common2(sql);
	}

	@Override
	public Map<String, Object> getBaseByTalent(String id) {
		return getBaseDetail(id);
	}

	@Override
	public Map<String, Object> getBaseDetail(String id) {
		StringBuffer sb = new StringBuffer();
		sb.append("ins1.id,");
		sb.append("ins1.code,");
		sb.append("ins1.name,");
		sb.append("ins1.level,");
		sb.append("ins1.type,");
		sb.append("ins1.city,");
		sb.append("ins1.county,");
		sb.append("ins2.name ins2name,");
		sb.append("ins2.code ins2code");
		String sql = "select " + sb.toString() + " from base ins1,institution ins2 where ins2.code=ins1.unit_code and ins1.id='" + id + "'";
		List<Object[]> ret = baseDao.common2(sql);
		Map<String, Object> map = new LinkedHashMap<>();
		if (CollectionUtils.isNotEmpty(ret)) {
			Object[] objects = ret.get(0);
			map.put("基地编号", objects[0]);
			map.put("基地编码", objects[1]);
			map.put("基地名称", objects[2]);
			map.put("基地级别", objects[3]);
			map.put("基地类型", objects[4]);
			map.put("所在城市", objects[5]);
			map.put("所在区县", objects[6]);
			map.put("所在机构", objects[7]);
			map.put("机构编码", objects[8]);
		}
		return map;
	}

	@Override
	public List<Object> findBaseLevel() {
		return baseDao.findBaseLevel();
	}

	@Override
	public List<Object> findBaseType() {
		return baseDao.findBaseType();
	}

	@Override
	public String[][] getCp(String totalid) {
		String[] id = totalid.split(";");
		String[][] data = new String[id.length][4];
		List<Object> query = null;

		for (int i = 0; i < id.length; i++) {
			if (StringUtils.isNotBlank(id[i])) {
				String[] quyu = id[i].split("@");
				String sql = "select count(*) from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins1.level = '国家级' and ";
				String sql1 = "select count(*) from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins1.level = '部属' and ";
				String sql2 = "select count(*) from base ins1,institution ins2 where ins1.unit_code = ins2.code and ins1.level = '省属' and";
				if (quyu[1].equals("city")) {
					sql += " ins2.city ='" + quyu[0] + "'";
					sql1 += " ins2.city ='" + quyu[0] + "'";
					sql2 += " ins2.city ='" + quyu[0] + "'";
				}
				if (quyu[1].equals("xian")) {
					sql += " ins2.county ='" + quyu[0] + "'";
					sql1 += " ins2.county ='" + quyu[0] + "'";
					sql2 += " ins2.county ='" + quyu[0] + "'";
				}
				data[i][0] = quyu[0];
				query = baseDao.common1(sql);
				if (query != null && query.size() != 0) {
					data[i][1] = query.get(0).toString();
				}
				query = baseDao.common1(sql1);
				if (query != null && query.size() != 0) {
					data[i][2] = query.get(0).toString();
				}
				query = baseDao.common1(sql2);
				if (query != null && query.size() != 0) {
					data[i][3] = query.get(0).toString();
				}
			}
		}
		return data;
	}

	public String getSum(String zhibiao) {
		Map<String, Long> data = getCount("", "", zhibiao);
		Set<Map<String, Object>> set = new LinkedHashSet<>();
		Map<String, Object> map;
		for (Map.Entry<String, Long> entry : data.entrySet()) {
			map = new HashMap<>();
			map.put("name", entry.getKey());
			map.put("value", entry.getValue());
			set.add(map);
		}
		return JacksonUtil.toJSon(set);
	}

	@Override
	public List<Object[]> getFb() {
		String sql = "SELECT ins2.lng,ins2.lat,COUNT(*),ins2.code FROM base ins1,institution ins2 WHERE ins1.unit_code = ins2.code GROUP BY code";
		return baseDao.common2(sql);
	}

	@Override
	public List<Map<String, Object>> getFb2() {
		String sql = "SELECT ins2.city,COUNT(*) FROM base ins1,institution ins2,city WHERE ins1.unit_code = ins2.code AND ins2.city = city.city_name GROUP BY ins2.city ";
		List<Object[]> list = baseDao.common2(sql);

		List<Map<String, Object>> ret = new ArrayList<>();
		Map<String, Object> map = null;
		for (Object[] objects : list) {
			map = new HashMap<>();
			map.put("name", objects[0]);
			map.put("value", objects[1]);
			ret.add(map);
		}
		return ret;
	}

}
